/*
 * postgresql c语言示例
 * 编译参考：
 * gcc demo_pqsql.c -o demo_pqsql
 *     -I/usr/local/Cellar/postgresql/9.4.1/include
 *     -L/usr/local/Cellar/postgresql/9.4.1/lib
 *     -lpq
 */

#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

static PGconn *s_pgconn;
static int s_init = 0;

int pqsql_init()
{
    char *dberr;
    char str[256];
    char conn_str[256];

    sprintf(conn_str, "hostaddr=%s port=%s dbname=%s",
        "127.0.0.1", "5432", "demo");

    /* 连接数据库 */
    s_pgconn = PQconnectdb(conn_str);
    if(PQstatus(s_pgconn) != CONNECTION_OK){
        dberr = PQerrorMessage(s_pgconn);
        if(strlen(dberr) < 200)
            sprintf(str, "connect postgresql failed: %s", dberr);
        else
            strcpy(str, "connect postgresql failed!");
        printf("%s\n", str);
        return -1;
    }

    printf("connect database %s ok.\n", "127.0.0.1");

    s_init = 1;

    return 0;
}

void pqsql_clear()
{
    PQfinish(s_pgconn);
    s_pgconn = 0;
    s_init = 0;
}

void test_query()
{
    int i,j,nFields;
    char query[1024];
    char str[512];
    char *ret,*dberr;
    int len,flag;
    PGresult* result;
    ExecStatusType est;

    if(!s_init){
        pqsql_init();
    }
    strcpy(query, "SELECT * FROM t_demo");

    /* 开始事务 */
    result = PQexec(s_pgconn, "BEGIN");
    if (PQresultStatus(result) != PGRES_COMMAND_OK) {
        dberr = PQerrorMessage(s_pgconn);
        if(strlen(dberr) < 200)
            sprintf(str, "PQexec failed: %s", dberr);
        else
            strcpy(str, "PQexec failed!");
        printf("%s\n", str);
        return;
    }
    PQclear(result);

    /* execute stored procedure */
    result = PQexec(s_pgconn, query);
    est = PQresultStatus(result);
    if (est == PGRES_TUPLES_OK || est == PGRES_COMMAND_OK) {
        /* 获取列名 */
        nFields = PQnfields(result);
        for (i = 0; i < nFields; ++i)
            printf("%-15s", PQfname(result, i));
        printf("\n\n");

        /* 输出所有行 */
        for (i = 0; i < PQntuples(result); ++i) {
            for (j = 0; j < nFields; ++j)
                printf("%-15s", PQgetvalue(result, i, j));
            printf("\n");
        }
    } else{
        dberr = PQerrorMessage(s_pgconn);
        if(strlen(dberr) < 200)
            sprintf(str, "PQexec failed: %s", dberr);
        else
            strcpy(str, "PQexec failed!");
        printf("%s\n", str);
        return;
    }
    PQclear(result);

    /* 事务结束 */
    result = PQexec(s_pgconn, "END");
    PQclear(result);
}

/* 测试存储过程 */
/*
 * oid 类型参考
 * 可以使用 SELECT oid,typname FROM pg_type获取
 * int4(32位):23
 * int8:20
 * char:18
 * text(string):25
 * float4(float):700
 * float8(double):701
 */
void test_prepare()
{
    int i,j,nFields;
    char query[1024];
    char str[512];
    char *ret,*dberr;
    int len,flag;
    PGresult* result;
    ExecStatusType est;
    /* 参数的类型 */
    Oid paramTypes[4] = {23, 23, 700, 25};
    /* 参数格式 1为二进制，0为字符 */
    int paramFormats[4] = {1, 1, 1, 0};
    /* 传入存储过程的值 */
    const char *paramValues[4];
    /* 数据长度 */
    int paramLengths[4];
    int param1,param2;
    float param3;
    char param4[256];

    if(!s_init){
        pqsql_init();
    }
    strcpy(query, "SELECT * FROM cf_test2($1,$2,$3,$4)");
    result = PQprepare(s_pgconn, "test2", query, 4, NULL);
    if (PQresultStatus(result) != PGRES_COMMAND_OK) {
        printf("PQprepare failed:%s", PQerrorMessage(s_pgconn));
        return;
    }

    /* 输入参数初始化(需要转为网络字节顺序) */
    param1 = htonl(1);
    param2 = htonl(2);
    param3 = 0.5;
    strcpy(param4, "hi");
    /* 设定参数值 */
    paramValues[0] = (char*)&param1;
    paramValues[1] = (char*)&param2;
    paramValues[2] = (char*)&param3;
    paramValues[3] = param4;
    /* 设定参数长度 */
    paramLengths[0] = sizeof(param1);
    paramLengths[1] = sizeof(param2);
    paramLengths[2] = sizeof(param3);
    paramLengths[3] = strlen(param4);
    result = PQexecPrepared(s_pgconn, "test2", 4, paramValues, paramLengths, paramFormats, 0);
    est = PQresultStatus(result);
    if (est == PGRES_TUPLES_OK || est == PGRES_COMMAND_OK) {
        /* 获取列名 */
        nFields = PQnfields(result);
        for (i = 0; i < nFields; ++i)
            printf("%-15s", PQfname(result, i));
        printf("\n\n");

        /* 输出所有行 */
        for (i = 0; i < PQntuples(result); ++i) {
            for (j = 0; j < nFields; ++j)
                printf("%-15s", PQgetvalue(result, i, j));
            printf("\n");
        }
    } else{
        printf("PQexecPrepared failed:%s\n", PQerrorMessage(s_pgconn));
    }
    PQclear(result);
}

int main(int argc, char** argv)
{
    pqsql_init();
    /* 执行普通查询 */
    test_query();
    /* 调用存储过程 */
    test_prepare();
    pqsql_clear();
    return 0;
}
